/**
 * @author Xiwi
 * @date 2021/8/12
 */
@RestController
public class DBController {
 
    @Resource
    private SqlSessionFactory sqlSessionFactory;
    @GetMapping("/db")
    public String db() throws SQLException {
 
        // 访问ip限制 可注释
        /*String ipAddr = IpUtils.getIpAddr(ServletUtils.getRequest());
        List<String> list = new ArrayList<String>() {{
            add("127.0.0.*");
        }}.stream().filter(item -> Pattern.matches(item, ipAddr)).collect(Collectors.toList());
        if (list.size() <= 0) { return "非法请求"; }*/
 
        SqlSession sqlSession = sqlSessionFactory.openSession();
        Connection connection = sqlSession.getConnection();
 
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Map<Pair<String, String>, List<Map<String, String>>> result = new LinkedHashMap<>();
        final String TABLE_SCHEMA = "数据库的名称";
        final String querySqlText = "SELECT C.TABLE_SCHEMA, T.TABLE_NAME, T.TABLE_COMMENT, C.COLUMN_NAME, C.COLUMN_COMMENT, C.ORDINAL_POSITION, IFNULL(C.COLUMN_DEFAULT, '') as COLUMN_DEFAULT, C.IS_NULLABLE, C.DATA_TYPE, C.CHARACTER_MAXIMUM_LENGTH, C.NUMERIC_PRECISION, C.NUMERIC_SCALE, C.COLUMN_TYPE, C.COLUMN_KEY, C.EXTRA \n" +
                "FROM information_schema.`TABLES` T\n" +
                "LEFT JOIN information_schema.`COLUMNS` C ON T.TABLE_NAME = C.TABLE_NAME  AND T.TABLE_SCHEMA = C.TABLE_SCHEMA \n" +
                "WHERE C.TABLE_SCHEMA = '" + TABLE_SCHEMA + "' \n" +
                "ORDER BY T.TABLE_NAME ASC";
//        System.out.println(querySqlText);
        preparedStatement = connection.prepareStatement(querySqlText);
        resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            String tableName = resultSet.getString("TABLE_NAME");
            String tableComment = resultSet.getString("TABLE_COMMENT");
            String columnName = resultSet.getString("COLUMN_NAME");
            String columnComment = resultSet.getString("COLUMN_COMMENT");
            String columnDefault = resultSet.getString("COLUMN_DEFAULT");
            String isNullable = resultSet.getString("IS_NULLABLE");
            String columnType = resultSet.getString("COLUMN_TYPE");
            String columnKey = resultSet.getString("COLUMN_KEY");
 
            Pair<String, String> key = Pair.of(tableName, tableComment);
            List<Map<String, String>> mapList = Optional.ofNullable(result.get(key)).orElse(new ArrayList<>());
            mapList.add(new HashMap<String, String>() {{
                put("columnName", columnName);
                put("columnComment", columnComment);
                put("columnDefault", columnDefault);
                put("isNullable", isNullable);
                put("columnType", columnType);
                put("columnKey", columnKey);
            }});
            result.put(key, mapList);
        }
 
        StringBuilder htmlText = new StringBuilder();
        String titleText = StringUtils.format("{}数据字典", TABLE_SCHEMA);
        htmlText.append("<head>");
        htmlText.append("<meta charset=\"UTF-8\"><title>" +TABLE_SCHEMA+ "</title>");
        htmlText.append("<link rel=\"stylesheet\" href=\"https://stackpath.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css\" integrity=\"sha384-HSMxcRTRxnN+Bdg0JdbxYKrThecOKuH5zCYotlSAcp1+c8xmyTe9GYg1l9a69psu\" crossorigin=\"anonymous\">");
        htmlText.append("<script src=\"https://stackpath.bootstrapcdn.com/bootstrap/3.4.1/js/bootstrap.min.js\" integrity=\"sha384-aJ21OjlMXNL5UyIl/XNwTMqvzeRMZH2w8c5cRVpzpU8Y5bApTppSuUkhZXN0VxHd\" crossorigin=\"anonymous\"></script>");
        htmlText.append("<style type=\"text/css\">.body{width:1000px;margin:0 auto;margin-top:100px}.body>p{font-size:80px;text-align:center;margin-bottom:80px}.body>.main{margin:60px 0}.body .table-name{font-size:40px;margin-right:10px}.body .table-comment{font-size:15px;margin-left:10px}</style>");
        htmlText.append("</head>");
 
        htmlText.append("<div class=\"body\">");
        htmlText.append(StringUtils.format("<p>{}</p>", titleText));
        result.entrySet()
                .forEach(table -> {
//                    System.out.println("表名: " + table.getKey().getFirst());
 
                    htmlText.append("<div class=\"main\">");
                    // 表头信息
                    htmlText.append(
                            StringUtils.format(
                                    "<div>\n" +
                                            "<span class=\"table-name\">{}</span>\n" +
                                            "<span class=\"table-comment\">{}</span>\n" +
                                            "</div>\n",
                                    table.getKey().getFirst(),
                                    table.getKey().getSecond()
                            )
                    );
 
                    StringBuilder tableColumnList = new StringBuilder();
                    table.getValue().forEach(item -> {
                        String columnName = item.get("columnName"); // 列名
                        String columnComment = item.get("columnComment"); // 列注释
                        String columnDefault = item.get("columnDefault"); // 缺省默认值
                        String isNullable = item.get("isNullable"); // 是否为空
                        String columnType = item.get("columnType"); // 列类型
                        String columnKey = item.get("columnKey"); // 主键
                        tableColumnList.append(
                                StringUtils.format(
                                        "<tr><td>{}</td><td>{}</td><td>{}</td><td>{}</td><td>{}</td></tr>",
                                        columnName, columnType, "NO".equals(isNullable) ? "是" : "否", columnDefault,columnComment)
                        );
                    });
 
                    htmlText.append(
                            StringUtils.format(
                                    "<table class=\"table table-hover table-bordered\"><thead>\n" +
                                            "<th>列名</th>\n" +
                                            "<th>列类型</th>\n" +
                                            "<th>是否必填</th>\n" +
                                            "<th>默认值</th>\n" +
                                            "<th>列注释</th>\n" +
                                            "</thead><tbody>{}</tbody></table>\n",
                                    tableColumnList.toString()
                            )
                    );
                    htmlText.append("</div>");
                });
        htmlText.append("</div>");
        return htmlText.toString();
    }
}